package com.wuzheng.wisgo.sanitation.cloud.repository.mapper.vehicleInfo;

import com.baomidou.mybatisplus.mapper.BaseMapper;
import com.wuzheng.wisgo.sanitation.cloud.model.tbox.pojo.ReceiveInfoSelectVehDto;
import com.wuzheng.wisgo.sanitation.cloud.model.vehicleInfo.EcharsShowDataSelectVehInfoDto;
import com.wuzheng.wisgo.sanitation.cloud.repository.entity.vehicle.BaVehicle;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.sql.Timestamp;
import java.util.List;

/**
 * 车辆信息 Mapper 接口
 *
 * @author sp
 * @since 2019-10-08
 */
public interface VehInfoDataMapper{
    /*
     * @Description: 车辆基本信息
     * @Author: sunpeng
     * @Date: 2019/12/5
    **/
    @Select("SELECT\n" +
            "ba_vehicle.id,ba_vehicle.veh_vin_code,ba_vehicle.veh_code,ba_vehicle.veh_brand_id,ba_vehicle.veh_brand_name,ba_vehicle.veh_audi,ba_vehicle.veh_motorcycle_type,\n" +
            "ba_vehicle.veh_grade,veh_grade_brand,ba_vehicle.vch_fuel_type_id,ba_vehicle.vch_fuel_type_name,ba_vehicle.vch_pip,\n" +
            "veh_chassis_code,veh_chassis_code_brand,ba_vehicle.chassis_model,\n" +
            "ba_vehicle.service_start_time,ba_vehicle.service_end_time\n" +
            "FROM ba_vehicle " +
            "WHERE veh_vin_code=#{vehVin}")
    BaVehicle selectByVehInfoToBase(@Param("vehVin") String vehVin);

    /*
     * @Description: 行驶里程数据查询
     * @Author: sunpeng
     * @Date: 2019/12/5
    **/
    @Select("SELECT to_char(tri.rec_collection_time, text'MM/dd') AS recCollectionTime ,\n" +
            "to_char(tri.rec_collection_time, text'yyyy/MM/dd') AS recCollectionTimeSort," +
            "(max(COALESCE(rec_total_miles,0))-min(COALESCE(rec_total_miles,0))) recTotalMiles\n" +
            "FROM t_receive_info tri " +
            "LEFT JOIN ba_vehicle ON tri.veh_id=ba_vehicle.id\n" +
            "WHERE tri.rec_collection_time>=#{startTime} \n" +
            "AND tri.rec_collection_time<=#{stopTime} " +
            "AND ba_vehicle.veh_vin_code=#{vehVin}\n" +
            "GROUP BY to_char(tri.rec_collection_time, text'yyyy/MM/dd'),to_char(tri.rec_collection_time, text'MM/dd')\n" +
            "ORDER BY to_char(tri.rec_collection_time, text'yyyy/MM/dd') DESC")
    List<EcharsShowDataSelectVehInfoDto> getRecTotalMilesFromDay(@Param("vehVin") String vehVin,@Param("startTime") Timestamp startTime, @Param("stopTime") Timestamp stopTime);

    /*
     * @Description: 每天所有的总里程数据
     * @Author: sunpeng
     * @Date: 2019/12/6
     **/
    @Select("SELECT DISTINCT\n" +
            "tri.veh_id,tri.rec_collection_time,to_char(tri.rec_collection_time, text'MM/dd') AS rec_collection_daytime," +
            "to_char(tri.rec_collection_time, text'yyyy/MM/dd') AS recCollectionTimeSort," +
            "tri.rec_job_status,tri.rec_total_miles\n" +
            "FROM t_receive_info tri\n" +
            "LEFT JOIN ba_vehicle ON tri.veh_id = ba_vehicle.ID \n" +
            "WHERE\n" +
            "ba_vehicle.veh_vin_code =#{vehVin}\n" +
            "AND tri.rec_collection_time >=#{startTime}\n" +
            "AND tri.rec_collection_time <=#{stopTime}")
    List<ReceiveInfoSelectVehDto> getWorkRecTotalMiles(@Param("vehVin") String vehVin, @Param("startTime") Timestamp startTime, @Param("stopTime") Timestamp stopTime);

    /*
     * @Description: 油耗数据
     * @Author: sunpeng
     * @Date: 2019/12/6
    **/
    @Select("SELECT DISTINCT tri.veh_id,tri.rec_collection_time,COALESCE(tri.rec_fuel_quantity,0) AS rec_fuel_quantity\n" +
            "FROM t_receive_info tri\n" +
            "LEFT JOIN ba_vehicle ON tri.veh_id = ba_vehicle.ID \n" +
            "WHERE \n" +
            "ba_vehicle.veh_vin_code =#{vehVin}\n" +
            "AND tri.rec_collection_time >=#{startTime}\n" +
            "AND tri.rec_collection_time <=#{stopTime}" +
            "ORDER BY tri.rec_collection_time ASC ")
    List<EcharsShowDataSelectVehInfoDto> getRecFuelQuantity(@Param("vehVin") String vehVin, @Param("startTime") Timestamp startTime, @Param("stopTime") Timestamp stopTime);

}
